{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# 1.使用 lightGBM 预测音乐推荐结果 训练数据进行特征工程"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import numpy as np\n",
    "import pickle as pk\n",
    "import seaborn as sns\n",
    "import matplotlib.pyplot as plt\n",
    "import math\n",
    "import scipy.io as sio\n",
    "import scipy.sparse as ss\n",
    "import re\n",
    "%matplotlib inline"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "data_path = '../Data/'  # 文件路径\n",
    "model_path = '../model/' # 模型路径"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 所有字段都转换成了类别特征的特征数据 加载到内存\n",
    "# 不包含这些特征 ['artist_name','composer','lyricist','name','genre_ids']\n",
    "with open(model_path + 'data_all_train_v2.pkl','rb') as fr:\n",
    "    data_all_train_v2 = pk.load(fr)\n",
    "fr.close()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>source_system_tab</th>\n",
       "      <th>source_screen_name</th>\n",
       "      <th>source_type</th>\n",
       "      <th>city</th>\n",
       "      <th>bd</th>\n",
       "      <th>gender</th>\n",
       "      <th>registered_via</th>\n",
       "      <th>song_length</th>\n",
       "      <th>language</th>\n",
       "      <th>reg_interval_days</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>201969</th>\n",
       "      <td>explore</td>\n",
       "      <td>Explore</td>\n",
       "      <td>online-playlist</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>gender_NaN</td>\n",
       "      <td>7</td>\n",
       "      <td>1</td>\n",
       "      <td>52</td>\n",
       "      <td>7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1932462</th>\n",
       "      <td>my library</td>\n",
       "      <td>Local playlist more</td>\n",
       "      <td>local-playlist</td>\n",
       "      <td>13</td>\n",
       "      <td>2</td>\n",
       "      <td>female</td>\n",
       "      <td>9</td>\n",
       "      <td>1</td>\n",
       "      <td>52</td>\n",
       "      <td>8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>183559</th>\n",
       "      <td>my library</td>\n",
       "      <td>Local playlist more</td>\n",
       "      <td>local-playlist</td>\n",
       "      <td>13</td>\n",
       "      <td>2</td>\n",
       "      <td>female</td>\n",
       "      <td>9</td>\n",
       "      <td>1</td>\n",
       "      <td>52</td>\n",
       "      <td>8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>149511</th>\n",
       "      <td>my library</td>\n",
       "      <td>Local playlist more</td>\n",
       "      <td>local-playlist</td>\n",
       "      <td>13</td>\n",
       "      <td>2</td>\n",
       "      <td>female</td>\n",
       "      <td>9</td>\n",
       "      <td>1</td>\n",
       "      <td>-1</td>\n",
       "      <td>8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>74867</th>\n",
       "      <td>explore</td>\n",
       "      <td>Explore</td>\n",
       "      <td>online-playlist</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>gender_NaN</td>\n",
       "      <td>7</td>\n",
       "      <td>1</td>\n",
       "      <td>52</td>\n",
       "      <td>7</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "        source_system_tab   source_screen_name      source_type city bd  \\\n",
       "201969            explore              Explore  online-playlist    1  1   \n",
       "1932462        my library  Local playlist more   local-playlist   13  2   \n",
       "183559         my library  Local playlist more   local-playlist   13  2   \n",
       "149511         my library  Local playlist more   local-playlist   13  2   \n",
       "74867             explore              Explore  online-playlist    1  1   \n",
       "\n",
       "             gender registered_via song_length language reg_interval_days  \n",
       "201969   gender_NaN              7           1       52                 7  \n",
       "1932462      female              9           1       52                 8  \n",
       "183559       female              9           1       52                 8  \n",
       "149511       female              9           1       -1                 8  \n",
       "74867    gender_NaN              7           1       52                 7  "
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data_all_train_v2.head()  "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(7377418, 10)"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data_all_train_v2.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "Int64Index: 7377418 entries, 201969 to 109449\n",
      "Data columns (total 10 columns):\n",
      "source_system_tab     category\n",
      "source_screen_name    category\n",
      "source_type           category\n",
      "city                  category\n",
      "bd                    category\n",
      "gender                category\n",
      "registered_via        category\n",
      "song_length           category\n",
      "language              category\n",
      "reg_interval_days     category\n",
      "dtypes: category(10)\n",
      "memory usage: 126.6 MB\n"
     ]
    }
   ],
   "source": [
    "data_all_train_v2.info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 所有字段都转换成了类别特征的特征数据 加载到内存\n",
    "with open(model_path + 'data_all_train.pkl','rb') as fr:\n",
    "    data_all_train = pk.load(fr)\n",
    "fr.close()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(7377418, 19)"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data_all_train.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>msno</th>\n",
       "      <th>song_id</th>\n",
       "      <th>source_system_tab</th>\n",
       "      <th>source_screen_name</th>\n",
       "      <th>source_type</th>\n",
       "      <th>target</th>\n",
       "      <th>city</th>\n",
       "      <th>bd</th>\n",
       "      <th>gender</th>\n",
       "      <th>registered_via</th>\n",
       "      <th>registration_init_time</th>\n",
       "      <th>expiration_date</th>\n",
       "      <th>song_length</th>\n",
       "      <th>genre_ids</th>\n",
       "      <th>artist_name</th>\n",
       "      <th>composer</th>\n",
       "      <th>lyricist</th>\n",
       "      <th>language</th>\n",
       "      <th>name</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>201969</th>\n",
       "      <td>FGtllVqz18RPiwJj/edr2gV78zirAiY/9SmYvia+kCg=</td>\n",
       "      <td>BBzumQNXUHKdEBOB7mAJuzok+IJA1c2Ryg/yzTF6tik=</td>\n",
       "      <td>explore</td>\n",
       "      <td>Explore</td>\n",
       "      <td>online-playlist</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>7</td>\n",
       "      <td>20120102</td>\n",
       "      <td>20171005</td>\n",
       "      <td>206471.0</td>\n",
       "      <td>359</td>\n",
       "      <td>Bastille</td>\n",
       "      <td>Dan Smith| Mark Crew</td>\n",
       "      <td>NaN</td>\n",
       "      <td>52.0</td>\n",
       "      <td>Good Grief</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1932462</th>\n",
       "      <td>Xumu+NIjS6QYVxDS4/t3SawvJ7viT9hPKXmf0RtLNx8=</td>\n",
       "      <td>bhp/MpSNoqoxOIB+/l8WPqu6jldth4DIpCm3ayXnJqM=</td>\n",
       "      <td>my library</td>\n",
       "      <td>Local playlist more</td>\n",
       "      <td>local-playlist</td>\n",
       "      <td>1</td>\n",
       "      <td>13</td>\n",
       "      <td>24</td>\n",
       "      <td>female</td>\n",
       "      <td>9</td>\n",
       "      <td>20110525</td>\n",
       "      <td>20170911</td>\n",
       "      <td>284584.0</td>\n",
       "      <td>1259</td>\n",
       "      <td>Various Artists</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>52.0</td>\n",
       "      <td>Lords of Cardboard</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>183559</th>\n",
       "      <td>Xumu+NIjS6QYVxDS4/t3SawvJ7viT9hPKXmf0RtLNx8=</td>\n",
       "      <td>JNWfrrC7zNN7BdMpsISKa4Mw+xVJYNnxXh3/Epw7QgY=</td>\n",
       "      <td>my library</td>\n",
       "      <td>Local playlist more</td>\n",
       "      <td>local-playlist</td>\n",
       "      <td>1</td>\n",
       "      <td>13</td>\n",
       "      <td>24</td>\n",
       "      <td>female</td>\n",
       "      <td>9</td>\n",
       "      <td>20110525</td>\n",
       "      <td>20170911</td>\n",
       "      <td>225396.0</td>\n",
       "      <td>1259</td>\n",
       "      <td>Nas</td>\n",
       "      <td>N. Jones、W. Adams、J. Lordan、D. Ingle</td>\n",
       "      <td>NaN</td>\n",
       "      <td>52.0</td>\n",
       "      <td>Hip Hop Is Dead(Album Version (Edited))</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>149511</th>\n",
       "      <td>Xumu+NIjS6QYVxDS4/t3SawvJ7viT9hPKXmf0RtLNx8=</td>\n",
       "      <td>2A87tzfnJTSWqD7gIZHisolhe4DMdzkbd6LzO1KHjNs=</td>\n",
       "      <td>my library</td>\n",
       "      <td>Local playlist more</td>\n",
       "      <td>local-playlist</td>\n",
       "      <td>1</td>\n",
       "      <td>13</td>\n",
       "      <td>24</td>\n",
       "      <td>female</td>\n",
       "      <td>9</td>\n",
       "      <td>20110525</td>\n",
       "      <td>20170911</td>\n",
       "      <td>255512.0</td>\n",
       "      <td>1019</td>\n",
       "      <td>Soundway</td>\n",
       "      <td>Kwadwo Donkoh</td>\n",
       "      <td>NaN</td>\n",
       "      <td>-1.0</td>\n",
       "      <td>Disco Africa</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>74867</th>\n",
       "      <td>FGtllVqz18RPiwJj/edr2gV78zirAiY/9SmYvia+kCg=</td>\n",
       "      <td>3qm6XTZ6MOCU11x8FIVbAGH5l5uMkT3/ZalWG1oo2Gc=</td>\n",
       "      <td>explore</td>\n",
       "      <td>Explore</td>\n",
       "      <td>online-playlist</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>7</td>\n",
       "      <td>20120102</td>\n",
       "      <td>20171005</td>\n",
       "      <td>187802.0</td>\n",
       "      <td>1011</td>\n",
       "      <td>Brett Young</td>\n",
       "      <td>Brett Young| Kelly Archer| Justin Ebach</td>\n",
       "      <td>NaN</td>\n",
       "      <td>52.0</td>\n",
       "      <td>Sleep Without You</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                                 msno  \\\n",
       "201969   FGtllVqz18RPiwJj/edr2gV78zirAiY/9SmYvia+kCg=   \n",
       "1932462  Xumu+NIjS6QYVxDS4/t3SawvJ7viT9hPKXmf0RtLNx8=   \n",
       "183559   Xumu+NIjS6QYVxDS4/t3SawvJ7viT9hPKXmf0RtLNx8=   \n",
       "149511   Xumu+NIjS6QYVxDS4/t3SawvJ7viT9hPKXmf0RtLNx8=   \n",
       "74867    FGtllVqz18RPiwJj/edr2gV78zirAiY/9SmYvia+kCg=   \n",
       "\n",
       "                                              song_id source_system_tab  \\\n",
       "201969   BBzumQNXUHKdEBOB7mAJuzok+IJA1c2Ryg/yzTF6tik=           explore   \n",
       "1932462  bhp/MpSNoqoxOIB+/l8WPqu6jldth4DIpCm3ayXnJqM=        my library   \n",
       "183559   JNWfrrC7zNN7BdMpsISKa4Mw+xVJYNnxXh3/Epw7QgY=        my library   \n",
       "149511   2A87tzfnJTSWqD7gIZHisolhe4DMdzkbd6LzO1KHjNs=        my library   \n",
       "74867    3qm6XTZ6MOCU11x8FIVbAGH5l5uMkT3/ZalWG1oo2Gc=           explore   \n",
       "\n",
       "          source_screen_name      source_type  target  city  bd  gender  \\\n",
       "201969               Explore  online-playlist       1     1   0     NaN   \n",
       "1932462  Local playlist more   local-playlist       1    13  24  female   \n",
       "183559   Local playlist more   local-playlist       1    13  24  female   \n",
       "149511   Local playlist more   local-playlist       1    13  24  female   \n",
       "74867                Explore  online-playlist       1     1   0     NaN   \n",
       "\n",
       "         registered_via  registration_init_time  expiration_date  song_length  \\\n",
       "201969                7                20120102         20171005     206471.0   \n",
       "1932462               9                20110525         20170911     284584.0   \n",
       "183559                9                20110525         20170911     225396.0   \n",
       "149511                9                20110525         20170911     255512.0   \n",
       "74867                 7                20120102         20171005     187802.0   \n",
       "\n",
       "        genre_ids      artist_name                                 composer  \\\n",
       "201969        359         Bastille                     Dan Smith| Mark Crew   \n",
       "1932462      1259  Various Artists                                      NaN   \n",
       "183559       1259              Nas     N. Jones、W. Adams、J. Lordan、D. Ingle   \n",
       "149511       1019         Soundway                            Kwadwo Donkoh   \n",
       "74867        1011      Brett Young  Brett Young| Kelly Archer| Justin Ebach   \n",
       "\n",
       "        lyricist  language                                     name  \n",
       "201969       NaN      52.0                               Good Grief  \n",
       "1932462      NaN      52.0                       Lords of Cardboard  \n",
       "183559       NaN      52.0  Hip Hop Is Dead(Album Version (Edited))  \n",
       "149511       NaN      -1.0                             Disco Africa  \n",
       "74867        NaN      52.0                        Sleep Without You  "
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data_all_train.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "{'artist_name',\n",
       " 'composer',\n",
       " 'expiration_date',\n",
       " 'genre_ids',\n",
       " 'lyricist',\n",
       " 'msno',\n",
       " 'name',\n",
       " 'registration_init_time',\n",
       " 'song_id',\n",
       " 'target'}"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "set(data_all_train.columns)-set(data_all_train_v2.columns)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [],
   "source": [
    "for column in ['artist_name','composer','genre_ids','lyricist','name']:\n",
    "    data_all_train[column] = data_all_train[column].astype('category')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "Int64Index: 7377418 entries, 201969 to 109449\n",
      "Data columns (total 19 columns):\n",
      "msno                      object\n",
      "song_id                   object\n",
      "source_system_tab         object\n",
      "source_screen_name        object\n",
      "source_type               object\n",
      "target                    int64\n",
      "city                      int64\n",
      "bd                        int64\n",
      "gender                    object\n",
      "registered_via            int64\n",
      "registration_init_time    int64\n",
      "expiration_date           int64\n",
      "song_length               float64\n",
      "genre_ids                 category\n",
      "artist_name               category\n",
      "composer                  category\n",
      "lyricist                  category\n",
      "language                  float64\n",
      "name                      category\n",
      "dtypes: category(5), float64(2), int64(6), object(6)\n",
      "memory usage: 988.9+ MB\n"
     ]
    }
   ],
   "source": [
    "data_all_train.info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# dataframe merge 失败\n",
    "# data_all_category_train = data_all_train_v2.merge(data_all_train_text_field, how='left',left_index=True,right_index=True)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [],
   "source": [
    "for column in ['artist_name','composer','genre_ids','lyricist','name']:\n",
    "    data_all_train_v2[column] = data_all_train[column]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>source_system_tab</th>\n",
       "      <th>source_screen_name</th>\n",
       "      <th>source_type</th>\n",
       "      <th>city</th>\n",
       "      <th>bd</th>\n",
       "      <th>gender</th>\n",
       "      <th>registered_via</th>\n",
       "      <th>song_length</th>\n",
       "      <th>language</th>\n",
       "      <th>reg_interval_days</th>\n",
       "      <th>artist_name</th>\n",
       "      <th>composer</th>\n",
       "      <th>genre_ids</th>\n",
       "      <th>lyricist</th>\n",
       "      <th>name</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>201969</th>\n",
       "      <td>explore</td>\n",
       "      <td>Explore</td>\n",
       "      <td>online-playlist</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>gender_NaN</td>\n",
       "      <td>7</td>\n",
       "      <td>1</td>\n",
       "      <td>52</td>\n",
       "      <td>7</td>\n",
       "      <td>Bastille</td>\n",
       "      <td>Dan Smith| Mark Crew</td>\n",
       "      <td>359</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Good Grief</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1932462</th>\n",
       "      <td>my library</td>\n",
       "      <td>Local playlist more</td>\n",
       "      <td>local-playlist</td>\n",
       "      <td>13</td>\n",
       "      <td>2</td>\n",
       "      <td>female</td>\n",
       "      <td>9</td>\n",
       "      <td>1</td>\n",
       "      <td>52</td>\n",
       "      <td>8</td>\n",
       "      <td>Various Artists</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1259</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Lords of Cardboard</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>183559</th>\n",
       "      <td>my library</td>\n",
       "      <td>Local playlist more</td>\n",
       "      <td>local-playlist</td>\n",
       "      <td>13</td>\n",
       "      <td>2</td>\n",
       "      <td>female</td>\n",
       "      <td>9</td>\n",
       "      <td>1</td>\n",
       "      <td>52</td>\n",
       "      <td>8</td>\n",
       "      <td>Nas</td>\n",
       "      <td>N. Jones、W. Adams、J. Lordan、D. Ingle</td>\n",
       "      <td>1259</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Hip Hop Is Dead(Album Version (Edited))</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>149511</th>\n",
       "      <td>my library</td>\n",
       "      <td>Local playlist more</td>\n",
       "      <td>local-playlist</td>\n",
       "      <td>13</td>\n",
       "      <td>2</td>\n",
       "      <td>female</td>\n",
       "      <td>9</td>\n",
       "      <td>1</td>\n",
       "      <td>-1</td>\n",
       "      <td>8</td>\n",
       "      <td>Soundway</td>\n",
       "      <td>Kwadwo Donkoh</td>\n",
       "      <td>1019</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Disco Africa</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>74867</th>\n",
       "      <td>explore</td>\n",
       "      <td>Explore</td>\n",
       "      <td>online-playlist</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>gender_NaN</td>\n",
       "      <td>7</td>\n",
       "      <td>1</td>\n",
       "      <td>52</td>\n",
       "      <td>7</td>\n",
       "      <td>Brett Young</td>\n",
       "      <td>Brett Young| Kelly Archer| Justin Ebach</td>\n",
       "      <td>1011</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Sleep Without You</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "        source_system_tab   source_screen_name      source_type city bd  \\\n",
       "201969            explore              Explore  online-playlist    1  1   \n",
       "1932462        my library  Local playlist more   local-playlist   13  2   \n",
       "183559         my library  Local playlist more   local-playlist   13  2   \n",
       "149511         my library  Local playlist more   local-playlist   13  2   \n",
       "74867             explore              Explore  online-playlist    1  1   \n",
       "\n",
       "             gender registered_via song_length language reg_interval_days  \\\n",
       "201969   gender_NaN              7           1       52                 7   \n",
       "1932462      female              9           1       52                 8   \n",
       "183559       female              9           1       52                 8   \n",
       "149511       female              9           1       -1                 8   \n",
       "74867    gender_NaN              7           1       52                 7   \n",
       "\n",
       "             artist_name                                 composer genre_ids  \\\n",
       "201969          Bastille                     Dan Smith| Mark Crew       359   \n",
       "1932462  Various Artists                                      NaN      1259   \n",
       "183559               Nas     N. Jones、W. Adams、J. Lordan、D. Ingle      1259   \n",
       "149511          Soundway                            Kwadwo Donkoh      1019   \n",
       "74867        Brett Young  Brett Young| Kelly Archer| Justin Ebach      1011   \n",
       "\n",
       "        lyricist                                     name  \n",
       "201969       NaN                               Good Grief  \n",
       "1932462      NaN                       Lords of Cardboard  \n",
       "183559       NaN  Hip Hop Is Dead(Album Version (Edited))  \n",
       "149511       NaN                             Disco Africa  \n",
       "74867        NaN                        Sleep Without You  "
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data_all_train_v2.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "Int64Index: 7377418 entries, 201969 to 109449\n",
      "Data columns (total 15 columns):\n",
      "source_system_tab     category\n",
      "source_screen_name    category\n",
      "source_type           category\n",
      "city                  category\n",
      "bd                    category\n",
      "gender                category\n",
      "registered_via        category\n",
      "song_length           category\n",
      "language              category\n",
      "reg_interval_days     category\n",
      "artist_name           category\n",
      "composer              category\n",
      "genre_ids             category\n",
      "lyricist              category\n",
      "name                  category\n",
      "dtypes: category(15)\n",
      "memory usage: 271.2 MB\n"
     ]
    }
   ],
   "source": [
    "data_all_train_v2.info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 将连续型变量离散化转为类别变量\n",
    "# 将原有的str 类型的变量转换为类别变量\n",
    "with open(model_path+'data_all_train_v3.pkl', 'wb') as fw:\n",
    "    pk.dump(data_all_train_v2,fw)\n",
    "fw.close()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [],
   "source": [
    "with open(model_path+'genre_id_df.pkl', 'rb') as fr:\n",
    "    genre_id_df = pk.load(fr)\n",
    "fr.close()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [],
   "source": [
    "for column in genre_id_df.columns:\n",
    "    data_all_train_v2[column]=genre_id_df[column]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>source_system_tab</th>\n",
       "      <th>source_screen_name</th>\n",
       "      <th>source_type</th>\n",
       "      <th>city</th>\n",
       "      <th>bd</th>\n",
       "      <th>gender</th>\n",
       "      <th>registered_via</th>\n",
       "      <th>song_length</th>\n",
       "      <th>language</th>\n",
       "      <th>reg_interval_days</th>\n",
       "      <th>...</th>\n",
       "      <th>genre_id_157</th>\n",
       "      <th>genre_id_158</th>\n",
       "      <th>genre_id_159</th>\n",
       "      <th>genre_id_160</th>\n",
       "      <th>genre_id_161</th>\n",
       "      <th>genre_id_162</th>\n",
       "      <th>genre_id_163</th>\n",
       "      <th>genre_id_164</th>\n",
       "      <th>genre_id_165</th>\n",
       "      <th>genre_id_166</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>201969</th>\n",
       "      <td>explore</td>\n",
       "      <td>Explore</td>\n",
       "      <td>online-playlist</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>gender_NaN</td>\n",
       "      <td>7</td>\n",
       "      <td>1</td>\n",
       "      <td>52</td>\n",
       "      <td>7</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1932462</th>\n",
       "      <td>my library</td>\n",
       "      <td>Local playlist more</td>\n",
       "      <td>local-playlist</td>\n",
       "      <td>13</td>\n",
       "      <td>2</td>\n",
       "      <td>female</td>\n",
       "      <td>9</td>\n",
       "      <td>1</td>\n",
       "      <td>52</td>\n",
       "      <td>8</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>183559</th>\n",
       "      <td>my library</td>\n",
       "      <td>Local playlist more</td>\n",
       "      <td>local-playlist</td>\n",
       "      <td>13</td>\n",
       "      <td>2</td>\n",
       "      <td>female</td>\n",
       "      <td>9</td>\n",
       "      <td>1</td>\n",
       "      <td>52</td>\n",
       "      <td>8</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>149511</th>\n",
       "      <td>my library</td>\n",
       "      <td>Local playlist more</td>\n",
       "      <td>local-playlist</td>\n",
       "      <td>13</td>\n",
       "      <td>2</td>\n",
       "      <td>female</td>\n",
       "      <td>9</td>\n",
       "      <td>1</td>\n",
       "      <td>-1</td>\n",
       "      <td>8</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>74867</th>\n",
       "      <td>explore</td>\n",
       "      <td>Explore</td>\n",
       "      <td>online-playlist</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>gender_NaN</td>\n",
       "      <td>7</td>\n",
       "      <td>1</td>\n",
       "      <td>52</td>\n",
       "      <td>7</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 182 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "        source_system_tab   source_screen_name      source_type city bd  \\\n",
       "201969            explore              Explore  online-playlist    1  1   \n",
       "1932462        my library  Local playlist more   local-playlist   13  2   \n",
       "183559         my library  Local playlist more   local-playlist   13  2   \n",
       "149511         my library  Local playlist more   local-playlist   13  2   \n",
       "74867             explore              Explore  online-playlist    1  1   \n",
       "\n",
       "             gender registered_via song_length language reg_interval_days  \\\n",
       "201969   gender_NaN              7           1       52                 7   \n",
       "1932462      female              9           1       52                 8   \n",
       "183559       female              9           1       52                 8   \n",
       "149511       female              9           1       -1                 8   \n",
       "74867    gender_NaN              7           1       52                 7   \n",
       "\n",
       "         ... genre_id_157 genre_id_158 genre_id_159 genre_id_160 genre_id_161  \\\n",
       "201969   ...            0            0            0            0            0   \n",
       "1932462  ...            0            0            0            0            0   \n",
       "183559   ...            0            0            0            0            0   \n",
       "149511   ...            0            0            0            0            0   \n",
       "74867    ...            0            0            0            0            0   \n",
       "\n",
       "         genre_id_162  genre_id_163  genre_id_164  genre_id_165  genre_id_166  \n",
       "201969              0             0             0             0             0  \n",
       "1932462             0             0             0             0             0  \n",
       "183559              0             0             0             0             0  \n",
       "149511              0             0             0             0             0  \n",
       "74867               0             0             0             0             0  \n",
       "\n",
       "[5 rows x 182 columns]"
      ]
     },
     "execution_count": 18,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data_all_train_v2.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "Int64Index: 7377418 entries, 201969 to 109449\n",
      "Columns: 182 entries, source_system_tab to genre_id_166\n",
      "dtypes: category(15), uint8(167)\n",
      "memory usage: 1.4 GB\n"
     ]
    }
   ],
   "source": [
    "data_all_train_v2.info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {},
   "outputs": [],
   "source": [
    "data_all_train_v2 = data_all_train_v2.drop(['genre_ids'],axis=1) # 对于 lightGBM 模型来说 genre_ids 字段如何处理比较合适"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(7377418, 181)"
      ]
     },
     "execution_count": 21,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data_all_train_v2.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 将拼接了 genre_ids 离散化后的数据保存到磁盘，其他特征都已经转为类别特征      (慢)\n",
    "# data_all_train_v2.to_csv(data_path+'data_all_train_v3.csv',index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 拼接了 genre_ids 离散化后的数据保存到磁盘，其他特征都已经转为类别特征    （快）\n",
    "with open(model_path + 'data_all_train_v4.pkl','wb') as fw:\n",
    "    pk.dump(data_all_train_v2,fw)\n",
    "fw.close()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.7.3"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}
